package com.jy.datapipeline.export.dao.impl;

import com.jy.datapipeline.common.constants.ExpConstants;
import com.jy.datapipeline.common.util.AssertUtil;
import com.jy.datapipeline.common.util.JdbcUtil;
import com.jy.datapipeline.export.dao.ExpRuleDao;
import com.jy.datapipeline.export.entity.ExpRuleModel;
import com.jy.datapipeline.export.entity.ExpTaskModel;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.*;

@Slf4j
@Repository
public class ExpRuleDaoImpl implements ExpRuleDao {
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public List<Map<String, Object>> findExpRuleByList(String dataSourceName, String ruleName,
        Integer iDisplayStart, Integer iDisplayLength) {
        List<Map<String, Object>> list=new ArrayList<>();
        try {
            StringBuffer sb=new StringBuffer();
            Map<String,Object> params = new HashMap<String,Object>();
            sb.append("SELECT a.id,a.rule_id,a.rule_name,a.data_source_id,a.col_separator,a.file_name_rule,"
                + " a.is_file,DATE_FORMAT(a.update_time,'%Y-%m-%d %H:%i:%s') as update_time"
                + " ,b.data_source_name from exp_rule a left join data_source_info b on a.data_source_id=b.data_source_id "
                + " where 1=1  ");
            if(dataSourceName!=null && !dataSourceName.equals("")){
                sb.append(" and b.data_source_name like :dataSourceName");
                params.put("dataSourceName","%"+dataSourceName+"%");
            }
            if(ruleName!=null && !ruleName.equals("")){
                 sb.append(" and a.rule_name like :ruleName ");
                 params.put("ruleName","%"+ruleName+"%");
            }
            sb.append(" order by a.update_time desc  ");
            if(iDisplayStart>-1 && iDisplayLength>-1){
                sb.append("LIMIT  :iDisplayStart ,:iDisplayLength ;");
                params.put("iDisplayStart",iDisplayStart);
                params.put("iDisplayLength",iDisplayLength);
            }

            list=this.namedParameterJdbcTemplate.queryForList(sb.toString(),params);
        }catch (Exception e){
            log.error("findDataStoreInfoByList exception",e);
        }

        return list;
    }

    @Override
    public int findExpRuleByCount(String dataSourceName, String ruleName) {
        List<Map<String, Object>> list=new ArrayList<>();
        int count=0;
        try {
            StringBuffer sb=new StringBuffer();
            Map<String,Object> params = new HashMap<String,Object>();
            sb.append("SELECT COUNT(a.id) as count from exp_rule a left join data_source_info b on a.data_source_id=b.data_source_id "
                + " where 1=1  ");
            if(dataSourceName!=null && !dataSourceName.equals("")){
                sb.append(" and b.data_source_name like :dataSourceName");
                params.put("dataSourceName","%"+dataSourceName+"%");
            }
            if(ruleName!=null && !ruleName.equals("")){
                sb.append(" and a.rule_name like :ruleName ");
                params.put("ruleName","%"+ruleName+"%");
            }
            sb.append(" order by a.update_time desc  ");
            list=this.namedParameterJdbcTemplate.queryForList(sb.toString(),params);
            if(list.size()>0){
                count=Integer.parseInt(list.get(0).get("count")!=null?list.get(0).get("count").toString():"0");
            }
        }catch (Exception e){
            log.error("findExpRuleByCount exception",e);
        }

        return count;

    }

    @Override
    public boolean updateExpRule(ExpRuleModel erm) {
        Map<String,Object> params=new HashMap<>();
        String sql=" update exp_rule set rule_name=:rule_name,"
            + " data_source_id=:data_source_id,col_separator=:col_separator,"
            + " file_name_rule=:file_name_rule,is_file=:is_file,update_time=NOW() "
            + " where rule_id=:rule_id";
        params.put("rule_name",erm.getRuleName());
        params.put("col_separator",erm.getColSeparator());
        params.put("data_source_id",erm.getDataSourceId());
        params.put("file_name_rule",erm.getFileNameRule());
        params.put("is_file",erm.getIsFile());
        params.put("rule_id",erm.getRuleId());
        int req=this.namedParameterJdbcTemplate.update(sql,params);
        return req>0?true:false;
    }

    @Override
    public boolean deleteByRuleId(String ruleId) {
        try{
            Map<String,Object> params=new HashMap<>();
            String sql="delete from exp_rule  where rule_id=:ruleId";
            params.put("ruleId",ruleId);
            int req=this.namedParameterJdbcTemplate.update(sql,params);
            return req>0?true:false;
        }catch (Exception e){
            log.error("deleteByRuleId error",e);
        } return false;
    }

    @Override
    public List<Map<String, Object>> findRuleInfoByTask(String ruleId, int status) {
        List<Map<String, Object>> list=new ArrayList<>();
        try{
            Map<String,Object> params=new HashMap<>();
            String sql="SELECT * from exp_rule a left JOIN exp_task b on a.rule_id=b.rule_id "
                + " where  a.rule_id=:ruleId";
            //params.put("status",status);
            params.put("ruleId",ruleId);
            list=this.namedParameterJdbcTemplate.queryForList(sql,params);
        }catch (Exception e){
            log.error("findRuleInfoByTask error",e);
        }
        return list;
    }

    @Override
    public Set<Integer> batchSave(List<ExpRuleModel> expRuleModelList) throws Exception {
        String sql = "INSERT INTO exp_rule (col_separator, data_source_id, is_file, rule_id, rule_name, update_time) VALUES (?, ?, ?, ?, ?, ?)";
        final JdbcUtil.JdbcConnectionPool jdbcConnectionPool = JdbcUtil.JdbcConnectionPool.getInstance();
        final Set<Integer> ids = new HashSet<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = jdbcConnectionPool.getConnection();
            preparedStatement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            //执行批量添加
            connection.setAutoCommit(false);
            this.executeBatchSave(expRuleModelList, preparedStatement);
            connection.commit();
            //获取主键集
            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            while (generatedKeys.next()) {
                ids.add(generatedKeys.getInt(1));
            }
        } catch (SQLException e) {
            connection.rollback();
            e.printStackTrace();
        }  finally {
            jdbcConnectionPool.release(connection, preparedStatement);
        }
        return ids;
    }

    private void executeBatchSave(List<ExpRuleModel> expRuleModelList, PreparedStatement preparedStatement) throws SQLException {
        for (ExpRuleModel expRuleModel : expRuleModelList) {
            preparedStatement.setString(1, expRuleModel.getColSeparator());
            preparedStatement.setString(2, expRuleModel.getDataSourceId());
            preparedStatement.setInt(3, expRuleModel.getIsFile());
            preparedStatement.setString(4, expRuleModel.getRuleId());
            preparedStatement.setString(5, expRuleModel.getRuleName());
            Timestamp timestamp = null;
            if (expRuleModel.getUpdate_time() != null) {
                timestamp = new Timestamp(expRuleModel.getUpdate_time().getTime());
            }
            preparedStatement.setTimestamp(6, timestamp);
            preparedStatement.addBatch();
        }
        int[] ints = preparedStatement.executeBatch();
        AssertUtil.totalAffectedRows(ints.length, expRuleModelList, ExpConstants.BATCH_ADD_FAIL);
    }

    @Override
    public void batchDelete(Set<Long> ids) throws Exception {
        List<Map<String, Object>> params = new ArrayList<>();
        Map<String, Object> param;
        for (Long id : ids) {
            param = new HashMap<>();
            param.put("id", id);
            params.add(param);
        }
        String sql = "delete from exp_rule where id=:id";
        int[] ints = namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(params));
        AssertUtil.totalAffectedRows(ints.length, ids, ExpConstants.BATCH_DELETE_FAIL);
    }

    @Override
    public Set<String> findExpRuleIdsByIds(Set<Long> ids) {
        Map<String,Object> params = new HashMap<>();
        params.put("ids", ids);
        String sql = "select rule_id from exp_rule where id in (:ids)";
        List<Map<String,Object>> list = namedParameterJdbcTemplate.queryForList(sql, params);
        Set<String> ruleIds = new HashSet<>();
        for (Map<String, Object> map : list) {
            ruleIds.add((String) map.get("rule_id"));
        }
        return ruleIds;
    }
}
